package sv.mecca.modelo;

import java.sql.SQLException;
import java.util.ArrayList;
import sv.mecca.modelo.Conexion;
import sv.mecca.form.TodosForm;

public class OperacionesTodos extends Conexion{

    public void insertarTodos(TodosForm t) throws SQLException{
        try {
            st = getConexion().createStatement();
            st.execute("insert into pacientes values(" +
                "'" + t.getExpediente() + "', " +
                "'" + t.getNombre() + "', " +
                "" + t.getEdad() + ", " +
                "'" + t.getSexo() + "', " +
                "'" + t.getProcedencia() + "')");
            st.execute("insert into lupus(expediente_paciente, fecha_diagnostico, patologias, complicaciones, talla, peso) values(" +
                "'" + t.getExpediente() + "', " +
                "'" + t.getFecha() + "', " +
                "'" + t.getPatologias()+ "', " +
                "'" + t.getComplicaciones() + "', " +
                "" + t.getTalla() + ", " +
                "" + t.getPeso() + ")");
            st.execute("insert into tratamientos(expediente_paciente, medicamento, fecha_inicio, fecha_fin, dosis, efectos) values(" +
                "'" + t.getExpediente() + "', " +
                "'" + t.getMedicamento()+ "', " +
                "'" + t.getFecha_inicio()+ "', " +
                "'" + t.getFecha_fin()+ "', " +
                "'" + t.getDosis()+ "', " +
                "'" + t.getEfectos()+ "')");
            st.execute("insert into examenes(expediente_paciente, gb, n, l, hb, ht, plt, tgo, tgp, coombs, proteina, anatinina) values(" +
                "'" + t.getExpediente() + "', " +
                "" + t.getGb()+ ", " +
                "" + t.getN()+ ", " +
                "" + t.getL()+ ", " +
                "" + t.getHb()+ ", " +
                "" + t.getHt()+ ", " +
                "" + t.getPlt()+ ", " +
                "" + t.getTgo()+ ", " +
                "" + t.getTgp()+ ", " +
                "'" + t.getCoombs()+ "', " +
                "" + t.getProteina()+ ", " +
                "" + t.getAnatinina()+ ")");
            st.execute("insert into ciclos(expediente_paciente, metilpredmisolona, mofetilo, c11, c12, c13, c21, c22, c23, c31, c32, c33, c41, c42, c43, c51, c52, c53, c61, c62, c63) values(" +
                "'" + t.getExpediente()+ "', " +
                "" + t.getMetilpredmisolona()+ ", " +
                "'" + t.getMofetilo()+ "', " +
                "'" + t.getC11() + "', " + t.getC12()+ ", '" + t.getC13() + "', " +
                "'" + t.getC21() + "', " + t.getC22()+ ", '" + t.getC23() + "', " +
                "'" + t.getC31() + "', " + t.getC32()+ ", '" + t.getC33() + "', " +
                "'" + t.getC41() + "', " + t.getC42()+ ", '" + t.getC43() + "', " +
                "'" + t.getC51() + "', " + t.getC52()+ ", '" + t.getC53() + "', " +
                "'" + t.getC61() + "', " + t.getC62()+ ", '" + t.getC63() + "')");
        } catch (Exception e) {
            System.out.println(e.getMessage());
            }
        finally{
            st.close();
            cn.close();
            }
        t.destroy();
        }
    public TodosForm modificarTodos(String ex) throws SQLException{
        TodosForm t = new TodosForm();
        try {
            st = getConexion().createStatement();
            rs = st.executeQuery("SELECT * FROM pacientes p "
                + "INNER JOIN lupus l ON p.expediente = l.expediente_paciente "
                + "INNER JOIN tratamientos t ON p.expediente = t.expediente_paciente "
                + "INNER JOIN examenes e ON p.expediente = e.expediente_paciente "
                + "INNER JOIN ciclos c ON p.expediente = c.expediente_paciente "
                + "where expediente like '" + ex + "'");
            rs.next();
            t.setExpediente(rs.getString(1));
            
            t.setNombre(rs.getString(2));
            t.setEdad(rs.getInt(3));
            t.setSexo(rs.getString(4));
            t.setProcedencia(rs.getString(5));
            
            t.setFecha(rs.getString(8));
            t.setPatologias(rs.getString(9));
            t.setComplicaciones(rs.getString(10));
            t.setTalla(rs.getDouble(11));
            t.setPeso(rs.getDouble(12));
            
            t.setMedicamento(rs.getString(15));
            t.setFecha_inicio(rs.getString(16));
            t.setFecha_fin(rs.getString(17));
            t.setDosis(rs.getString(18));
            t.setEfectos(rs.getString(19));
            
            t.setGb(rs.getDouble(22));
            t.setN(rs.getDouble(23));
            t.setL(rs.getDouble(24));
            t.setHb(rs.getDouble(25));
            t.setHt(rs.getDouble(26));
            t.setPlt(rs.getDouble(27));
            t.setTgo(rs.getDouble(28));
            t.setTgp(rs.getDouble(29));
            t.setCoombs(rs.getString(30));
            t.setProteina(rs.getDouble(31));
            t.setAnatinina(rs.getDouble(32));
            
            t.setMetilpredmisolona(rs.getDouble(35)); t.setMofetilo(rs.getString(36));
            t.setC11(rs.getString(37)); t.setC12(rs.getDouble(38)); t.setC13(rs.getString(39));
            t.setC21(rs.getString(40)); t.setC22(rs.getDouble(41)); t.setC23(rs.getString(42));
            t.setC31(rs.getString(43)); t.setC32(rs.getDouble(44)); t.setC33(rs.getString(45));
            t.setC41(rs.getString(46)); t.setC42(rs.getDouble(47)); t.setC43(rs.getString(48));
            t.setC51(rs.getString(49)); t.setC52(rs.getDouble(50)); t.setC53(rs.getString(51));
            t.setC61(rs.getString(52)); t.setC62(rs.getDouble(53)); t.setC63(rs.getString(54));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            }
        finally{
            rs.close();
            st.close();
            cn.close();
            return t;
            }
        }
    public void actualizarTodos(TodosForm t, String ex) throws SQLException{
        try {
            st = getConexion().createStatement();
            st.executeUpdate("update pacientes set " +
                "expediente = '" + t.getExpediente() + "', " +
                "nombre = '" + t.getNombre() + "', " +
                "edad = " + t.getEdad() + ", " +
                "sexo = '" + t.getSexo() + "', " +
                "procedencia = '" + t.getProcedencia() + "' " +
                "where expediente like '" + ex + "'");
            st.executeUpdate("update lupus set " +
                "fecha_diagnostico = '" + t.getFecha() + "', " +
                "patologias = '" + t.getPatologias() + "', " +
                "complicaciones = '" + t.getComplicaciones() + "', " +
                "talla = " + t.getTalla() + ", " +
                "peso = " + t.getPeso() + " " +
                "where expediente_paciente like '" + ex + "'");
            st.executeUpdate("update tratamientos set " +
                "medicamento = '" + t.getMedicamento()+ "', " +
                "fecha_inicio = '" + t.getFecha_inicio()+ "', " +
                "fecha_fin = '" + t.getFecha_fin()+ "', " +
                "dosis = '" + t.getDosis()+ "', " +
                "efectos = '" + t.getEfectos()+ "' " +
                "where expediente_paciente like '" + ex + "'");
            st.executeUpdate("update examenes set " +
                "gb = " + t.getGb()+ ", " +
                "n = " + t.getN()+ ", " +
                "l = " + t.getL()+ ", " +
                "hb = " + t.getHb()+ ", " +
                "ht = " + t.getHt()+ ", " +
                "plt = " + t.getPlt()+ ", " +
                "tgo = " + t.getTgo()+ ", " +
                "tgp = " + t.getTgp()+ ", " +
                "coombs = '" + t.getCoombs()+ "', " +
                "proteina = " + t.getProteina()+ ", " +
                "anatinina = " + t.getAnatinina()+ " " +
                "where expediente_paciente like '" + ex + "'");
            st.executeUpdate("update ciclos set " +
                "metilpredmisolona = '" + t.getMetilpredmisolona()+ "', " +
                "mofetilo = '" + t.getMofetilo()+ "', " +
                "c11 = '" + t.getC11() + "', " +
                "c12 = " + t.getC12() + ", " +
                "c13 = '" + t.getC13() + "', " +
                "c21 = '" + t.getC21() + "', " +
                "c22 = " + t.getC22() + ", " +
                "c23 = '" + t.getC23() + "', " +
                "c31 = '" + t.getC31() + "', " +
                "c32 = " + t.getC32() + ", " +
                "c33 = '" + t.getC33() + "', " +
                "c41 = '" + t.getC41() + "', " +
                "c42 = " + t.getC42() + ", " +
                "c43 = '" + t.getC43() + "', " +
                "c51 = '" + t.getC51() + "', " +
                "c52 = " + t.getC52() + ", " +
                "c53 = '" + t.getC53() + "', " +
                "c61 = '" + t.getC61() + "', " +
                "c62 = " + t.getC62() + ", " +
                "c63 = '" + t.getC63() + "' " +
                "where expediente_paciente like '" + ex + "'");
        } catch (Exception e) {
            System.out.println(e.getMessage());
            }
        finally{
            st.close();
            cn.close();
            }
        t.destroy();
        }
    public void EliminarTodos(String ex) throws SQLException{
        try {
            st = getConexion().createStatement();
            st.execute("delete from pacientes where expediente = '" + ex + "'");
        } catch (Exception e) {
            System.out.println(e.getMessage());
            }
        finally{
            st.close();
            cn.close();
            }
        }
    public ArrayList<TodosForm> listado() throws SQLException{
        ArrayList<TodosForm> todos = null;
        try {
            st = getConexion().createStatement();
            rs = st.executeQuery("SELECT * FROM pacientes p "
                + "INNER JOIN lupus l ON p.expediente = l.expediente_paciente "
                + "INNER JOIN tratamientos t ON p.expediente = t.expediente_paciente "
                + "INNER JOIN examenes e ON p.expediente = e.expediente_paciente "
                + "INNER JOIN ciclos c ON p.expediente = c.expediente_paciente order by expediente");
            todos = new ArrayList<TodosForm>();
            while (rs.next()) {
                TodosForm t = new TodosForm(rs.getString(1),
                        rs.getString(2), rs.getInt(3), rs.getString(4), rs.getString(5),
                        rs.getString(8), rs.getString(9), rs.getString(10), rs.getDouble(11), rs.getDouble(12),
                        rs.getString(15), rs.getString(16), rs.getString(17), rs.getString(18), rs.getString(19),
                        rs.getDouble(22), rs.getDouble(23), rs.getDouble(24), rs.getDouble(25), rs.getDouble(26), rs.getDouble(27), rs.getDouble(28), rs.getDouble(29), rs.getString(30), rs.getDouble(31), rs.getDouble(32),

                        rs.getDouble(35), rs.getString(36),
                        rs.getString(37), rs.getDouble(38), rs.getString(39),
                        rs.getString(40), rs.getDouble(41), rs.getString(42),
                        rs.getString(43), rs.getDouble(44), rs.getString(45),
                        rs.getString(46), rs.getDouble(47), rs.getString(48),
                        rs.getString(49), rs.getDouble(50), rs.getString(51),
                        rs.getString(52), rs.getDouble(53), rs.getString(54));
                todos.add(t);
                }
            rs.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
            }
        finally{
            st.close();
            cn.close();
            return todos;
            }
        }
    }